package com.lanhao.aot.iot;

import java.io.File;
import java.nio.charset.Charset;
import java.util.List;
import java.util.function.Function;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;

public class ChannelDataFlushUtil {
	
	/**
	 * 有异常iot_account清单
	 */
	public static String excelChannelFilePath = "D://南京电信频道_20200430.xls"; 
	/**
	 * 最终生成脚本的存储路径
	 */
	public static String scriptFilePath = "D://script.sql";
	public static String LINE_SEPARATOR = "\r\n";

	public static void main(String[] args) {
		try {
			ExcelReader reader = ExcelUtil.getReader(new File(excelChannelFilePath), 0);
			List<List<Object>> dataList = reader.read();
			StringBuilder sb = new StringBuilder();
			for (int i = 1; i < dataList.size(); i++) {
				List<Object> list = dataList.get(i);
				String id = String.valueOf(list.get(0));
				String providerId = String.valueOf(list.get(1));
				String serialNum = String.valueOf(list.get(2));
				String channelName = String.valueOf(list.get(3));
				if (StringUtils.isNotBlank(providerId) && StringUtils.isNotBlank(serialNum)) {
					Number count = Db.use().queryNumber(
							"select count(*) from t_remote_channel_info t where t.provider_id = ? and t.serial_number = ? ",
							providerId, serialNum);
					String sql = StringUtils.EMPTY;
					Pair<String, StringBuilder> apply = generatesQlFunc.apply(count);
					String left = apply.getLeft();
					StringBuilder right = apply.getRight();
					if ("U".equals(left)) {
						sql = StrUtil.format(right, channelName, providerId, serialNum);
					} else if ("I".equals(left)) {
						sql = StrUtil.format(right, id, providerId, serialNum, channelName);
					}
					sb.append(sql);
				}
			}
			FileUtil.appendString(sb.toString(), new File(scriptFilePath), Charset.forName("UTF-8"));
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	static Function<Number, Pair<String, StringBuilder>> generatesQlFunc = count -> {
		StringBuilder insertTemplate = new StringBuilder().append(
				"insert into t_remote_channel_info(id, provider_id, serial_number, channel_name, state, create_time, update_time) values ")
				.append("('{}','{}','{}','{}','1',now(), now());").append(LINE_SEPARATOR);

		StringBuilder updateTemplate = new StringBuilder().append(
				"update t_remote_channel_info t set t.channel_name = '{}', t.update_time=now() where t.provider_id='{}' and t.serial_number='{}';")
				.append(LINE_SEPARATOR);

		if (count.intValue() > 0) {
			return Pair.of("U", updateTemplate);
		}
		return Pair.of("I", insertTemplate);
	};

}
